﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace Hanhud.Data.Providers
{
    internal class CProvider
    {
        private static object _syncRoot = new object();

        private static volatile CProvider _instance = null;
        public static CProvider Instance
        {
            get
            {
                if (_instance == null)
                {
                    lock (_syncRoot)
                    {
                        if (_instance == null)
                        {
                            _instance = new CProvider();
                        }
                    }
                }
                return _instance;
            }
        }

        private CDbAccess _db = null;

        private CProvider()
        {
            _db = new CDbAccess();
        }

        public DataRow GetLocationInfo(int locationID)
        {
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Locations_GetLocationInfoByID"));
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@LocationID", SqlDbType.Int).Value = locationID;

            DataTable tblLocation = new DataTable("Locations");
            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                IDataReader reader = _db.ExecuteReader(cmd, CommandBehavior.SingleRow);
                tblLocation.Load(reader, LoadOption.Upsert);
            }

            return tblLocation.Rows.Count > 0 ? tblLocation.Rows[0] : null;
        }

        public DataRow GetLocationInfo(int provinceCode, int districtCode, int commenuCode)
        {
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Locations_GetLocationInfo"));
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@ProvinceCode", SqlDbType.Int).Value = provinceCode;
            cmd.Parameters.Add("@DistrictCode", SqlDbType.Int).Value = districtCode;
            cmd.Parameters.Add("@CommuneCode", SqlDbType.Int).Value = commenuCode;

            DataTable tblLocation = new DataTable("Locations");
            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                IDataReader reader = _db.ExecuteReader(cmd, CommandBehavior.SingleRow);
                tblLocation.Load(reader, LoadOption.Upsert);
            }

            return tblLocation.Rows.Count > 0 ? tblLocation.Rows[0] : null;
        }

        public DataTable GetLocations(int provinceCode, int districtCode)
        {
            DataTable tblLocation = new DataTable("Locations");

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Locations_GetLocations"));
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@ProvinceCode", SqlDbType.Int).Value = provinceCode;
            cmd.Parameters.Add("@DistrictCode", SqlDbType.Int).Value = districtCode;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                IDataReader reader = _db.ExecuteReader(cmd, CommandBehavior.SingleResult);
                tblLocation.Load(reader, LoadOption.Upsert);
            }

            return tblLocation;
        }
    }
}
